package cn.tedu.shoppingmall.mapper;

import cn.tedu.shoppingmall.vo.ComTrans;
import cn.tedu.shoppingmall.vo.Commodity;
import cn.tedu.shoppingmall.vo.Transaction;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
//订单数据库增删改查操作
@Repository
@Mapper
public interface TransactionMapper {
    //新增商品信息等到订单表中
    @Insert("insert into transaction values(null,#{state},#{username},#{userid},#{comid},#{price},#{comsum},#{created},#{UUID})")
    int payShopCar(Transaction transaction);
    //新增信息订单商品详情表中(comtrans)  -->为了以后点击单个订单时,能够查询所购买过的商品
    @Insert("insert into comtrans(name,intro,sum,price,comId,classId,url,transUUID) values(#{name},#{intro},#{sum},#{price}" +
            ",#{comId},#{classId},#{url},#{transUUID})")
    int payShopCarToComtrans(ComTrans comTrans);

    //删除订单,--->改变可见状态
    @Update("update transaction set state=0 where id=#{id}")
    int deleteTransactionOne(@Param("id") int transactionId);
    //修改订单商品表中商品的数量  -->    因为支付后,订单商品表中的商品信息都是通过商品id在commodity表查出来的,所以得需要单独修改
    @Update("update transaction set comsum=#{comsum} where id=#{id}")
    int updateTransactionComsum(@Param("id") int transactionId,@Param("comsum") int comsum);
    //根据用户id改变用户名  -->个人信息修改后,同步用
    @Update("update transaction set username=#{username} where userid=#{userid}")
    void updateUsernameByUserid(String username, Integer userid);


    //多弄了个UUID列,为了在插入数据后,得不到数据id,代替id称为唯一列,查询所有信息
    //等修改成了xml文件后或者使用Mybatis-plus,就不用这个了(因为能直接获取)
    @Select("select * from comtrans where transUUID=#{transUUID}")
    List<ComTrans> getComTransAllByTransUUID(String transUUID);
    //根据uuid查transactionid
    @Select("select id from transaction where UUID=#{uuid}")
    Integer getTransactionIdByUuid(String uuid);
    //根据transactionid查总商品数
    @Select("select SUM(sum) from comtrans where transUUID=#{UUID}")
    Integer getComTransCountByTransactionUUID(String UUID);
    //根据用户id查询订单表中的所属所有信息
    @Select("select * from transaction where userid=#{userid}")
    List<Transaction> getTransAll(int userid);
    //根据userid查消费过总商品数
    @Select("select sum(comsum) from transaction where username=#{username}")
    Integer getTransComsumByUsername(String username);
    //根据userid查消费过总消费额
    @Select("select sum(price) from transaction where username=#{username}")
    Double getTransPriceByUsername(String username);
    //根据订单id查询订单表中单个订单的信息
    @Select("select * from transaction where id=#{id}")
    Transaction getTransOne(@Param("id") int transid);
    //查询管理首页的销售额
    @Select("select sum(price) from transaction")
    Integer SelectByComPriceAll();
    //查询管理首页今日销售额
    @Select("select sum(price) from transaction where created like '${created}%'")
    Integer SelectByComPriceOneDay(@Param("created") String created);
    //查询管理首页本月销售额
    @Select("select sum(price) from transaction where created between #{hour} and #{day}")
    Integer SelectByComPriceHour(@Param("day") String day,@Param("hour") String hour);
    //查询管理首页本年销售额
    @Select("select sum(price) from transaction where created between #{year} and #{day}")
    Integer SelectByComPriceYear(@Param("day") String day,@Param("year") String year);
    //查询管理首页的销售数量
    @Select("select sum(comsum) from transaction")
    Integer SelectByComSumAll();
    //查询管理首页今日销售数量
    @Select("select sum(comsum) from transaction where created like '${created}%'")
    Integer SelectByComSumDay(@Param("created") String created);
    //查询管理首页本月销售数量
    @Select("select sum(comsum) from transaction where created between #{hour} and #{day}")
    Integer SelectByComSumHour(@Param("day") String day,@Param("hour") String hour);
    //查询管理首页本年销售数量
    @Select("select sum(comsum) from transaction where created between #{year} and #{day}")
    Integer SelectByComSumYear(@Param("day") String day,@Param("year") String year);
    //查询在comtrans表中,单个商品的销售记录
    @Select("select * from comtrans where comId=#{comId}")
    List<ComTrans> SelectToComTransAll(int comId);
    //查询在comtrans表中,单个商品的销售记录
    @Select("select * from comtrans where comId=#{comId} and (created between #{date1} and #{date2})")
    List<ComTrans> SelectToComTransAllByDate(int comId, LocalDateTime date1, LocalDateTime date2);
}
